﻿using System;
using System.Data;
using System.Data.Common;

using Community.CsharpSqlite.SQLiteClient;

using VSTalk.Engine.Core.Data;

namespace VSTalk.Engine.Core.ContactInfo
{
    internal class ContactInfoDA : IDataAccess
    {
        private SqliteConnection _connection;

        public void Init(SqliteConnection connection)
        {
            _connection = connection;
        }

        public void AddRecord(string jid, string hash, string fullname, long ticks, string url, string description, string photoType, byte[] photoBin)
        {
            _connection.Open();
            var cmd = _connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO  contact_details(jid, hash, fullname, url, birthday, details, photoType, photoBin) 
VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)
";
            AddParam(cmd, "@p1", DbType.StringFixedLength, jid);
            AddParam(cmd, "@p2", DbType.StringFixedLength, hash);
            AddParam(cmd, "@p3", DbType.StringFixedLength, fullname);
            AddParam(cmd, "@p4", DbType.StringFixedLength, url);
            AddParam(cmd, "@p5", DbType.Int64, ticks);
            AddParam(cmd, "@p6", DbType.String, description);
            AddParam(cmd, "@p7", DbType.StringFixedLength, photoType);
            AddParam(cmd, "@p8", DbType.Binary, photoBin);
            
            cmd.ExecuteNonQuery();
            _connection.Close();
        }

        private void AddParam(DbCommand cmd, string name, DbType type, object value)
        {
            var p1 = new SqliteParameter(name, type);
            p1.Value = value;
            cmd.Parameters.Add(p1);
        }

        public bool HasPhoto(string jid, string hash)
        {
            _connection.Open();
            var cmd = _connection.CreateCommand();
            cmd.CommandText = @"
select exists(select 1 from contact_details where jid=@p1 and hash=@p2 limit 1)
";
            AddParam(cmd, "@p1", DbType.StringFixedLength, jid);
            AddParam(cmd, "@p2", DbType.StringFixedLength, hash);

            var reader = cmd.ExecuteReader();
            reader.Read();
            var result = reader.GetBoolean(0);
            _connection.Close();
            return result;
        }

        public Tuple<string, string, long, string, string, byte[]> GetLatest(string jid)
        {
            _connection.Open();
            var cmd = _connection.CreateCommand();
            cmd.CommandText = @"
select fullname, url, birthday, details, photoType, photoBin
from contact_details where jid=@p1 order by Id desc limit 1
";
            AddParam(cmd, "@p1", DbType.StringFixedLength, jid);
            
            var reader = cmd.ExecuteReader();
            Tuple<string, string, long, string, string, byte[]> result = null;
            if (reader.Read())
            {
                var fullname = reader.IsDBNull(0) ? null : reader.GetString(0);
                var url = reader.IsDBNull(1) ? null : reader.GetString(1);
                var birthday = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                var details = reader.IsDBNull(3) ? null : reader.GetString(3);
                var phototype = reader.IsDBNull(4) ? null : reader.GetString(4);
                var photobin = reader.IsDBNull(5) ? null : (byte[])reader.GetValue(5);
                result = Tuple.Create(fullname, url, birthday, details, phototype, photobin);
            }
            _connection.Close();
            return result;
        }
    }
}